﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;

namespace footwear
{
    public class CategoryDao
    {
        public List<Category> GetListCategory()
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            var list = new List<Category>();
            try
            {
                var myCommand = new MySqlCommand("GetListCategory;", DbConnect.Connection) { CommandType = CommandType.StoredProcedure };
                MySqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    var category = new Category()
                    {
                        Id = Convert.ToInt32(reader["Id"].ToString()),
                        Type = reader["Type"].ToString(),
                        TypeEng = reader["TypeEng"].ToString(),
                        ShowIndex = Convert.ToInt32(reader["ShowIndex"].ToString())
                    };
                    list.Add(category);
                }
                DbConnect.CloseConnection();
            }
            catch (MySqlException)
            {
                DbConnect.CloseConnection();
            }
            return list;
        }

        public Category GetCategoryById(string id)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            var item = new Category();
            try
            {
                string query = "SELECT Id, Type, TypeEg, ShowIndex FROM category WHERE Id=?ItemId";
                var myCommand = new MySqlCommand(query, DbConnect.Connection);
                myCommand.Parameters.AddWithValue("ItemId", id);
                MySqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    item.Id = Convert.ToInt32(reader["Id"].ToString());
                    item.Type = reader["Type"].ToString();
                    item.TypeEng = reader["TypeEng"].ToString();
                    item.ShowIndex = Convert.ToInt32(reader["ShowIndex"].ToString());
                }
                DbConnect.CloseConnection();
            }
            catch (MySqlException)
            {
                DbConnect.CloseConnection();
            }
            return item;
        }

        public List<Category> GetListShowIndexCategory()
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            var list = new List<Category>();
            try
            {
                var myCommand = new MySqlCommand("GetListCategory;", DbConnect.Connection) { CommandType = CommandType.StoredProcedure };
                MySqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    if (Convert.ToInt32(reader["ShowIndex"].ToString()) == 1)
                    {
                        var category = new Category
                        {
                            Id = Convert.ToInt32(reader["Id"].ToString()),
                            Type = reader["Type"].ToString(),
                            TypeEng = reader["TypeEng"].ToString(),
                            ShowIndex = Convert.ToInt32(reader["ShowIndex"].ToString())
                        };
                        list.Add(category);
                    }
                }
                DbConnect.CloseConnection();
            }
            catch (MySqlException)
            {
                DbConnect.CloseConnection();
            }
            return list;
        }

        public string GetType(int id)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            var type = "";
            var typeEng = "";
            try
            {
                var myCommand = new MySqlCommand("GetListCategory;", DbConnect.Connection) { CommandType = CommandType.StoredProcedure };
                MySqlDataReader reader = myCommand.ExecuteReader();
                while (reader.Read())
                {
                    if (Convert.ToInt32(reader["Id"].ToString()) == id)
                    {
                        type = reader["Type"].ToString();
                        typeEng = reader["TypeEng"].ToString();
                    }
                }
                DbConnect.CloseConnection();
            }
            catch (MySqlException)
            {
                DbConnect.CloseConnection();
            }
            return type + "|" + typeEng;
        }

        public bool UpdateCategoryById(string id, string type, string showIndex)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            try
            {
                string query = "UPDATE category SET Type=?type, ShowIndex=?showIndex WHERE Id=?id";
                var myCommand = new MySqlCommand(query, DbConnect.Connection);
                myCommand.Parameters.AddWithValue("id", id);
                myCommand.Parameters.AddWithValue("type", type);
                //myCommand.Parameters.AddWithValue("typeEng", typeEng);
                myCommand.Parameters.AddWithValue("showIndex", showIndex);
                try
                {
                    myCommand.ExecuteNonQuery();
                    DbConnect.CloseConnection();
                    return true;
                }
                catch
                {
                    DbConnect.CloseConnection();
                    return false;
                }
            }
            catch (MySqlException)
            {
                return false;
            }
        }

        public bool AddCategoryById(string type, string showIndex)
        {
            try
            {
                DbConnect.CloseConnection();
            }
            catch { }
            DbConnect.OpenConnection();
            try
            {
                string query = "INSERT INTO category (Type, ShowIndex) VALUES (?type, ?showIndex)";
                var myCommand = new MySqlCommand(query, DbConnect.Connection);
                myCommand.Parameters.AddWithValue("type", type);
                myCommand.Parameters.AddWithValue("showIndex", showIndex);
                try
                {
                    myCommand.ExecuteNonQuery();
                    DbConnect.CloseConnection();
                    return true;
                }
                catch
                {
                    DbConnect.CloseConnection();
                    return false;
                }
            }
            catch (MySqlException)
            {
                return false;
            }
        }
    }
}